<?php

class mscan_history extends CI_Model {

    /**
     * ambil record pada table t_trx_scan_in berdasarkan id
     * @param scan_in_id $id
     * @return type 
     */
    public function getById($id) {
        $id = intval($id);
        $dbconn = $this->load->database("default", TRUE);
		$sql = "SELECT scan_in_id, user_id, scan_date, istatus, created_by, created_on, updated_by, ".
		 		"updated_on, acess_via, date_trunc('second', created_on::time ) as data_timestamp " .
				"FROM t_trx_scan_in " .
                "WHERE scan_in_id='$id'";

        $query = $dbconn->query($sql);
        
        //$query = $this->db->where('scan_in_id', $id)->limit(1)->get('t_trx_scan_in');

        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            return array();
        }
    }

    public function getPdfDetails($scan_id){
        $listItem = array();
		$dbconn = $this->load->database("default", TRUE);
		$userlogin = $this->session->userdata('username');

        $sql =  "SELECT imp.item_code as item_code, count(imp.item_code) as jumlah, ".
                "itm.item_name, grp.item_group_name, f_get_price(imp.item_code, h.user_id) as harga ".
                "FROM t_trx_scan_in_detail d ".
                "JOIN t_mtr_item itm ON itm.iccid = split_part(d.iccid,'#',1) ".
                "JOIN t_mtr_item_mapping imp ON itm.item_code = imp.item_code ".
                "JOIN t_mtr_item_group grp ON imp.item_group_id = grp.item_group_id ".
                "JOIN t_trx_scan_in h ON d.scan_in_id = h.scan_in_id ".
                "WHERE h.scan_in_id = '$scan_id' ".
                "GROUP BY imp.item_code, itm.item_name, grp.item_group_id, h.user_id";

		$query = $dbconn->query($sql);
        return $query->result();
    }


    public function getIccidByScanId($scan_id) {
        $dbconn = $this->load->database("default", TRUE);
        $userlogin = $this->session->userdata('username');
        // $sql = "SELECT split_part(iccid,'#',1) ".
        //         "FROM t_trx_scan_in_detail ".
        //         "WHERE scan_in_id = $scan_id ";

// SELECT split_part(d.iccid,'#',1) AS iccid, itm.item_name, itm.item_code, grp.item_group_id, itm.mdn,
// CAST(f_get_price(split_part(d.iccid,'#',1), '10003752') AS INTEGER), grp.item_group_name 
// FROM t_trx_scan_in_detail d
// JOIN t_mtr_item itm ON itm.iccid = split_part(d.iccid,'#',1)
// JOIN t_mtr_item_mapping imp ON itm.item_code = imp.item_code
// JOIN t_mtr_item_group grp ON imp.item_group_id = grp.item_group_id
// WHERE d.scan_in_id ='168'

        $sql = "SELECT split_part(d.iccid,'#',1) iccid, itm.item_name, itm.item_code, grp.item_group_id, itm.mdn, ".
                "CAST(f_get_price(split_part(d.iccid,'#',1), h.user_id) AS INTEGER), grp.item_group_name " .
                "FROM t_trx_scan_in_detail d ".
                "join t_trx_scan_in as h on h.scan_in_id = d.scan_in_id ".
                "JOIN t_mtr_item itm ON itm.iccid = split_part(d.iccid,'#',1) ".
                "JOIN t_mtr_item_mapping imp ON itm.item_code = imp.item_code " .
                "JOIN t_mtr_item_group grp ON imp.item_group_id = grp.item_group_id " .
                "WHERE d.scan_in_id =' $scan_id' ";

        $query = $dbconn->query($sql);
        return $query->result();
    }

    //ambil record pada table t_mtr_item dan t_mtr_item_group
    public function getItemDetail($id) {
        $listItem = array();
        //anggwa@gmail.com 20120513
        $dbconn = $this->load->database("default", TRUE);
        $userlogin = $this->session->userdata('username');
        $sql = "SELECT itm.item_name, itm.item_code, grp.item_group_id, itm.mdn, " .
                "CAST(f_get_price(itm.iccid, '$userlogin') AS INTEGER), grp.item_group_name " .
                "FROM t_mtr_item itm " .
                "JOIN t_mtr_item_mapping imp ON itm.item_code = imp.item_code " .
                "JOIN t_mtr_item_group grp ON imp.item_group_id = grp.item_group_id " .
                "WHERE itm.iccid = split_part('$id','#',1) LIMIT 1";
        $query = $dbconn->query($sql);
        if ($query->num_rows() > 0) {
            return $listItem = $query->result_array();
        } else {
            return array();
        }
        // $this->db->select('item_group_name');
        // $this->db->where('item_group_id', $res[0]['item_group_id']);
        // $q = $this->db->get('t_mtr_item_group');
        // $q = $q->result_array();	
        // $userlogin = $this->session->userdata('username');
        // $this->db->select("item_name, item_code, item_group_id, mdn, f_get_price(item_code, '$userlogin')");
        // $this->db->where('iccid', $id);
        // $q = $this->db->get('t_mtr_item');
        // $listItem = $q->result_array();	
        // $this->db->select('item_group_name');
        // $this->db->where('item_group_id', $listItem[0]['item_group_id']);
        // $q = $this->db->get('t_mtr_item_group');
        // $q = $q->result_array();	
        // $listItem[0]['item_group_name'] = $q[0]['item_group_name'];
        return $listItem;
    }

    //ambil semua record pada table t_trx_scan_in
    public function getAllScanin() {

        $res = array();
        //anggwa@gmail.com 20120513
        /**
        /* edited by: alfin akhret <alfin.akhret@gmail.com>
        /* di-SSP kan
        */
        $dbconn = $this->load->database("default", TRUE);
        $userlogin = $this->session->userdata('username');
        $query = "select * from f_get_cluster('".$userlogin."')";
        $query = $dbconn->query($query);
        $query = $query->result();
        $cluster = $query[0]->f_get_cluster;

        /** ssp variable **/
        $sIndexColumn = "scan_in_id"; //index column untuk kecepatan akses data
        $defaultOrder = "0"; //default order berdasarkan field yg ada pada $aColumns
        $aColumns = array("scan_date", "data_timestamp", "scan_in_id", "user_name"); //field2 yg akan ditampilkan pada dataTab
        $flag = '';

//         $sql = "SELECT * FROM t_trx_scan_in " .
//                "WHERE DATE_PART('day', now() - scan_date::timestamp) " .
//                "BETWEEN 0 AND DATE_PART('day',now() - (now() - interval '1 month')) " .
//                "AND f_get_cluster(user_id) = f_get_cluster('$userlogin') " .
//                "ORDER BY scan_in_id DESC, scan_date desc";

         $inputQuery = "
            SELECT ".str_replace(" , ", " ", implode(", ", $aColumns))."
            FROM v_ssp_scan_in_history WHERE scan_date BETWEEN CURRENT_DATE - integer '8' AND CURRENT_DATE - integer '1' AND f_get_cluster(user_id) = '$cluster'";
         
    //      $sql = "SELECT scan_in_id, user_id, scan_date,  " .
		 	// 	"date_trunc('second', created_on::time ) as data_timestamp, " .
				// "created_on FROM t_trx_scan_in " .
    //             "WHERE scan_date BETWEEN CURRENT_DATE - integer '8' AND CURRENT_DATE - integer '1' " .
				// "AND f_get_cluster(user_id) = '$cluster' " .
    //             "ORDER BY scan_in_id DESC, scan_date desc";

        return $this->serversidedatatables->executeServerSideProcess($defaultOrder, $flag, $userlogin, $inputQuery, $aColumns, $sIndexColumn);
        

        // $query = $dbconn->query($sql);
        //$query = $this->db->get( 't_trx_scan_in' );


        // if ($query->num_rows() > 0) {
        //     foreach ($query->result() as $res) {
        //         $res->getNikName = $this->getNikName($res->user_id);
        //     }
        //     return $query->result();
        // } else {
        //     return array();
        // }
    }

//edited by Alfin Akhret
public function getAllScaninCurrentUser() {

        $res = array();
        //anggwa@gmail.com 20120513
        $dbconn = $this->load->database("default", TRUE);
        $userlogin = $this->session->userdata('username');
//         $sql = "SELECT * FROM t_trx_scan_in " .
//                "WHERE DATE_PART('day', now() - scan_date::timestamp) " .
//                "BETWEEN 0 AND DATE_PART('day',now() - (now() - interval '1 month')) " .
//                "AND f_get_cluster(user_id) = f_get_cluster('$userlogin') " .
//                "ORDER BY scan_in_id DESC, scan_date desc";
         // $sql = "SELECT scan_in_id, user_id, scan_date,  " .
         //        "date_trunc('second', created_on::time ) as data_timestamp, " .
         //        "created_on FROM t_trx_scan_in " .
         //        "WHERE scan_date BETWEEN CURRENT_DATE - integer '8' AND CURRENT_DATE - integer '1' " .
         //        "AND user_id = '$userlogin' " .
         //        "ORDER BY scan_in_id DESC, scan_date desc";

         /** ssp variable **/
        $sIndexColumn = "scan_in_id"; //index column untuk kecepatan akses data
        $defaultOrder = "0"; //default order berdasarkan field yg ada pada $aColumns
        $aColumns = array("scan_date", "data_timestamp", "scan_in_id", "user_name"); //field2 yg akan ditampilkan pada dataTab
        $flag = '';

         $inputQuery = "
            SELECT ".str_replace(" , ", " ", implode(", ", $aColumns))."
            FROM v_ssp_scan_in_history WHERE scan_date BETWEEN CURRENT_DATE - integer '8' AND CURRENT_DATE - integer '1' AND user_id = '$userlogin'";

        // $query = $dbconn->query($sql);
        //$query = $this->db->get( 't_trx_scan_in' );

        return $this->serversidedatatables->executeServerSideProcess($defaultOrder, $flag, $userlogin, $inputQuery, $aColumns, $sIndexColumn);
        
        // if ($query->num_rows() > 0) {
        //     foreach ($query->result() as $res) {
        //         $res->getNikName = $this->getNikName($res->user_id);
        //     }
        //     return $query->result();
        // } else {
        //     return array();
        // }
    }


//end getAll
    //save hasil insert dari form ke dalam table t_trx_scan_in 
    function saveScan() {

        $dblokal = $this->load->database("default", true);
        $scan_date = $this->input->post("scan_date");
        $sales_id = $this->input->post("sales_id");
        $quantity = $this->input->post("quantity");
        $remark = $this->input->post("remark");
        $salesIdExplode = explode(" ", $sales_id);

        $data = array(
            "scan_date" => $scan_date,
            "created_by" => $this->session->userdata('username'),
            "updated_by" => $this->session->userdata('username'),
            "user_id" => trim($salesIdExplode[0]),
            "acess_via" => "web",
            "istatus" => 1
        );
        $dblokal->insert("t_trx_scan_in", $data);
        $last = $this->db->insert_id();
        $this->mglobal->saveLog('scan-in', 'create', $last);

        return $last;
    }

    //save hasil insert dari form ke dalam table t_trx_scan_in_detail
    function saveScanInDetail($sid, $i) {

        $dblokal = $this->load->database("default", true);
        $iccid = $this->input->post('iccid');
        //$mdn = $this->input->post('mdn');
        $item_group_name = $this->input->post('item_group_name');
        $item_name = $this->input->post('item_name');

        $data = array(
            "scan_in_id" => $sid,
            "iccid" => $iccid[$i],
            "istatus" => 1
        );

        $dblokal->insert("t_trx_scan_in_detail", $data);
        $last = $this->db->insert_id();
        $this->mglobal->saveLog('scan-in-detail', 'create', '(header=' . $sid . ' detail=' . $last . ' iccid=' . $iccid[$i] . ')');

        //update istatus pada table t_mtr_item menjadi 2
        $data2 = array(
            'istatus' => 2,
        );
        $this->db->update('t_mtr_item', $data2, array('iccid' => $iccid[$i]));
    }

    //ambil nama territory dari id user yang bersangkutan
    public function getClusterName($id = '') {
        $dbconn = $this->load->database("default", TRUE);
        $sql = "SELECT f_get_territory_path(f_get_cluster('$id')) as territory_name, f_get_cluster('$id') as territory_id";
        $query = $dbconn->query($sql);
        if ($query->num_rows() > 0) {
            return $query->result_array();
        } else {
            return array();
        }



        // $this->db->select('territory_name, territory_id');
        // $this->db->from('t_mtr_territory');
        // $this->db->where('user_id', $id);
        // $this->db->limit(1, 0);
        // $query = $this->db->get();
        // return $query->result_array();
    }

    //ambil daftar sales id yang terdaftar pada territory yang sama dengan user yang sedang login
    public function getSalesID($curLogin = "") {
        //filter di t_mtr_user istatus = 1 -- tambah 2 line (AND usr.istatus = 1)
        $dbconn = $this->load->database("default", TRUE);
        $userLogin = $this->session->userdata('username');
        // $sql = "SELECT DISTINCT ter.user_id, usr.user_name
        // FROM t_mtr_territory ter
        // JOIN t_mtr_user usr on ter.user_id = usr.user_id
        // AND ter.parent_id = f_get_cluster('$userlogin')
        // AND usr.istatus = 1
        // UNION
        // SELECT DISTINCT ut.user_id, usr.user_name
        // FROM t_mtr_user_territory ut
        // JOIN t_mtr_user usr on ut.user_id = usr.user_id
        // AND usr.reporting_to = '$userlogin'
        // AND usr.istatus = 1
        // ORDER BY user_name";
        // $sql = "SELECT user_id, CONCAT_WS(' || ',user_id,initcap(user_name)) AS user_name
        // $sql = "SELECT user_id, CONCAT_WS(' || ',user_id,initcap(user_name)) AS user_name " .
        //         "FROM t_mtr_user " .
        //         "WHERE reporting_to = '$userLogin' " .
        //         "AND istatus = 1 " .
        //         "ORDER BY t_mtr_user.user_name ASC";

        //$query = $dbconn->query($sql);
        $query = $dbconn->query("SELECT usr.user_id, CONCAT_WS(' | ',usr.user_id,initcap(usr.user_name),grp.user_group_caption) as user_name
                                FROM t_mtr_user usr
                                JOIN t_mtr_user_group grp ON usr.user_group_id = grp.user_group_id
                                WHERE grp.parent_id = 4
                                AND f_get_cluster(usr.user_id) = f_get_cluster('$userLogin')
                                AND usr.istatus = 1
                                ORDER BY usr.user_name ASC;");

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $res) {
                //$res->availability = $this->getOutletList($res->user_id);
                $res->availability = "available";

            }
            return $query->result();
        } else {
            return array();
        }
    }

    function getOutletList($sales_id) {
        $userGroup = $this->checkUserGroup($sales_id);

        if ($userGroup == "Direct Sales") {
            $curYm = date("YW");
        } else if ($userGroup == "Smart Fren Ambassador") {
            $curYm = date("Ym");
        } else {
            return array();
        }
        $curDate = (integer) date("d");

        if ($this->db->table_exists('t_jc_' . $sales_id)) {
            $this->db->select('channel_id');
            $this->db->where('d' . $curDate, '1');
            $this->db->where('yyyymm', $curYm);
            $q = $this->db->get('t_jc_' . $sales_id);
        }
        else
            return "unavailable";

        if ($q->num_rows() > 0) {
            return "available";
        } else {
            //return "unavailable";
            return "available";
        }
    }

    function checkUserGroup($sales_id) {
        $this->db->select('grp.user_group_name');
        $this->db->from('t_mtr_user as usr');
        $this->db->join('t_mtr_user_group as grp', 'usr.user_group_id = grp.user_group_id');
        $this->db->where('usr.user_id', $sales_id);
        $q = $this->db->get();

        if ($q->num_rows() > 0) {
            $q = $q->result_array();
            return $q[0]['user_group_name'];
        } else {
            return array();
        }
    }

    //ambil record-record yang berkaitan dengan proses pembuatan scan bring in product
    function getScan($iccid, $sales_id) {
        $dbconn = $this->load->database("default", TRUE);
        $userlogin = $this->session->userdata('username');
        $listItem = array();

        //$sql = "SELECT itm.mdn, itm.item_name, grp.item_group_name, itm.default_price
        $sql = "SELECT itm.mdn, itm.item_name, grp.item_group_name, CAST(f_get_price(itm.item_code, '$userlogin') AS INTEGER) " .
                "FROM t_mtr_item itm " .
                "JOIN t_mtr_item_mapping imap ON itm.item_code = imap.item_code " .
                "JOIN t_mtr_item_group grp ON imap.item_group_id = grp.item_group_id " .
                "WHERE itm.iccid = split_part('$iccid','#',1) LIMIT 1";

        $query = $dbconn->query($sql);


        if ($query->num_rows() > 0) {
            foreach ($query->result() as $res) {
                $res->getPrice = $this->getPrice($iccid, $sales_id);
                $res->valid = $this->checkValid($iccid, $sales_id);
                // $res->icc = $iccid;
                // $res->sal = $sales_id;
            }
            return $query->result();
        } else {
            return array();
        }
    }

    // // Ambil Credit Limit (maksimum) dari Sales ($sales_id)
    function getCreditLimit($sales_id) {
        $dbconn = $this->load->database("default", TRUE);
        $sql = ("SELECT f_get_credit_limit('$sales_id')");
        $query = $dbconn->query($sql);
        return $query->result();
    }

    // Ambil Current Credit Limit dari Sales ($sales_id)
    function getCurrentLimit($sales_id) {
        $dbconn = $this->load->database("default", TRUE);
        $sql = ("SELECT f_get_current_limit('$sales_id')");
        $query = $dbconn->query($sql);
        return $query->result();
    }

    //get nik name from user
    function getNikName($userID) {
        $dbconn = $this->load->database("default", TRUE);
        $sql = "SELECT f_get_nik_name('$userID')";
        $query = $dbconn->query($sql);

        if ($query->num_rows() > 0) {
            $res = $query->result_array();
            return $res[0]['f_get_nik_name'];
        } else {
            return array();
        }
    }

    //get item price for each sales
    function getPrice($iccid = "", $userID = "") {
        $dbconn = $this->load->database("default", TRUE);
        $sql = "SELECT CAST(f_get_price(split_part('$iccid','#',1),'$userID') AS INTEGER)";
        $query = $dbconn->query($sql);

        if ($query->num_rows() > 0) {
            $res = $query->result_array();
            return $res[0]['f_get_price'];
        } else {
            return array();
        }
    }

    //get item price for each sales
    function checkValid($iccid = "", $userID = "") {
        $dbconn = $this->load->database("default", TRUE);
        $sql = "SELECT f_check_valid_item(split_part('$iccid','#',1),'$userID')";
        $query = $dbconn->query($sql);

        if ($query->num_rows() > 0) {
            $res = $query->result_array();
            return $res[0]['f_check_valid_item'];
        } else {
            return array();
        }
    }

    //ambil daftar iccid dari barang-barang yang berada pada cluster yang bersangkutan, untuk ditampilkan pada textbox autocomplete
    function getRelatedIccid($salesId = "", $term = "") {
        //itm.distributor_id = dis.distributor_id		
        $userlogin = $this->session->userdata('username');
        $dbconn = $this->load->database("default", TRUE);

        $sql = "select itm.iccid " .
                "from t_mtr_item as itm " .
                "join t_mtr_distributor as dis on " .
                "itm.distributor_id = dis.distributor_id " .
                "AND dis.territory_id = f_get_cluster('$userlogin')  " .
                "where itm.iccid like '$term%' " .
                "AND itm.istatus = 1 " .
                "ORDER BY iccid ASC LIMIT 20";

        $query = $dbconn->query($sql);

        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return array();
        }
    }

}

//end class